﻿USE master;
GO

-- delete QLDNData if it exit
IF EXISTS(SELECT * FROM sys.databases WHERE name ='QLDN')
BEGIN
	DROP DATABASE QLDN;
END

-- creat database QLDNData
CREATE DATABASE QLDN;

USE QLDN
GO

-- 1 create table VAITRO 
CREATE TABLE VAITRO
	(STT int identity not null,
	MaVaiTro AS CAST('VT'+RIGHT('0000'+ CAST(STT AS VARCHAR(4)), 4) AS VARCHAR(10)) PERSISTED primary key,
	TenVaiTro VARCHAR(50) not null,
	DienGiai VARCHAR(MAX) null,
	TrangThai BIT not null
	)
GO

-- 2 create table CHUCNANG 
CREATE TABLE CHUCNANG
	(STT INT IDENTITY NOT NULL,
	MaChucNang AS CAST('CN'+RIGHT('0000'+CAST(STT AS VARCHAR(4)), 4) AS VARCHAR(10)) PERSISTED PRIMARY KEY,
	TenChucNang VARCHAR(50) not null
	)
GO

-- 2 create table QUYENHAN 
CREATE TABLE QUYENHAN
	(STT INT IDENTITY NOT NULL,
	MaQuyenHan AS CAST('QH'+RIGHT('0000'+CAST(STT AS VARCHAR(4)), 4) AS VARCHAR(10)) PERSISTED PRIMARY KEY,
	TenQuyenHan VARCHAR(50) not null,
	TrangThai BIT not null
	)
GO

-- 4 create table NGUOIDUNG 
CREATE TABLE NGUOIDUNG
	(STT INT IDENTITY NOT NULL,
	MaNguoiDung AS CAST('ND'+RIGHT('0000'+CAST(STT AS VARCHAR(4)), 4) AS VARCHAR(10)) PERSISTED PRIMARY KEY,
	MaVaiTro VARCHAR(10) FOREIGN KEY(MaVaiTro) REFERENCES VAITRO(MaVaiTro),
	TenNguoiDung VARCHAR(50) not null,
	TaiKhoan VARCHAR(50) not null,
	MatKhau VARCHAR(50) not null,
	DienGiai VARCHAR(MAX) null,
	TrangThai BIT not null,
	NhomNguoiDung VARCHAR(50) not null
	)
GO

-- 5 create table VAITRO_CHUCNANG
CREATE TABLE VAITRO_CHUCNANG 
	(MaVaiTro VARCHAR(10) FOREIGN KEY(MaVaiTro) REFERENCES VAITRO(MaVaiTro) not null,
	MaChucNang VARCHAR(10) FOREIGN KEY(MaChucNang) REFERENCES CHUCNANG(MaChucNang) not null,
	CONSTRAINT PK_VAITRO_CHUCNANG PRIMARY KEY CLUSTERED (MaVaiTro, MaChucNang)
	)
GO

-- 6 create table CHUCNANG_QUYENHAN
CREATE TABLE CHUCNANG_QUYENHAN
	(MaChucNang VARCHAR(10) FOREIGN KEY(MaChucNang) REFERENCES CHUCNANG(MaChucNang) not null,
	MaQuyenHan VARCHAR(10) FOREIGN KEY(MaQuyenHan) REFERENCES QUYENHAN(MaQuyenHan) not null,
	CONSTRAINT PK_CHUCNANG_QUYENHAN PRIMARY KEY CLUSTERED (MaChucNang, MaQuyenHan)
	)
GO

-- Phan Thai ---------------------------------------------------------------- 

-- 7 Create table KHU VUC
CREATE TABLE KHUVUC
	(STT INT IDENTITY NOT NULL,
	MaKhuVuc AS CAST('KV'+RIGHT('000'+CAST(STT AS VARCHAR(3)),3) AS VARCHAR(10)) PERSISTED PRIMARY KEY,
	TenKhuVuc VARCHAR(255) NOT NULL,
	GhiChu VARCHAR(MAX) NULL,
	ConQuanLy BIT NOT NULL
	)
GO

-- 8 Create table LOAIHANGHOA
CREATE TABLE LOAIHANGHOA
	(STT INT IDENTITY NOT NULL,
	MaLoaiHangHoa AS CAST('LHH'+RIGHT('0000'+CAST(STT AS VARCHAR(4)),4) AS VARCHAR(10)) PERSISTED PRIMARY KEY,
	TenLoaiHangHoa VARCHAR(255) NOT NULL,
	GhiChu VARCHAR(MAX) NULL
	)
GO

-- 9 Create table DONVITINH
CREATE TABLE DONVITINH
	(STT INT IDENTITY NOT NULL,
	MaDonViTinh AS CAST('DVT'+RIGHT('0000'+CAST(STT AS VARCHAR(4)),4) AS VARCHAR(10)) PERSISTED PRIMARY KEY,
	TenDonViTinh VARCHAR(255) NOT NULL,
	MoTa VARCHAR(MAX) NULL,
	ConQuanLy BIT NOT NULL
	)
GO

-- 10 Create table NHACUNGCAP
CREATE TABLE NHACUNGCAP
	(STT INT IDENTITY NOT NULL,
	MaNhaCungCap AS CAST('NCC'+RIGHT('0000'+CAST(STT AS VARCHAR(4)),4) AS VARCHAR(10)) PERSISTED PRIMARY KEY,
	TenNhaCungCap VARCHAR(255) NOT NULL,
	DiaChi VARCHAR(MAX) NULL,
	DienThoai VARCHAR(50) NULL,
	Fax VARCHAR(50) NULL,
	Mail VARCHAR(50) NULL,
	DiDong VARCHAR(50) NULL,
	WebSite VARCHAR(MAX) NULL,
	NguoiLienHe VARCHAR(255) NULL,
	ChucVu VARCHAR(255) NULL,
	NickFaceBook VARCHAR(255) NULL,
	NickSkype VARCHAR(255) NULL,
	TaiKhoan VARCHAR(50) NULL,
	NganHang VARCHAR(50) NULL,
	GioiHanNo MONEY NULL,
	ChietKhau FLOAT NULL,
	NoHienTai MONEY NULL,
	MoTa VARCHAR(MAX) null,
	ConQuanLy BIT NOT NULL
	)
GO

-- 11 Create table HANGHOA
CREATE TABLE HANGHOA
	(STT INT IDENTITY NOT NULL,
	MaHangHoa AS CAST('HH'+RIGHT('00000000'+CAST(STT AS VARCHAR(8)),8) AS VARCHAR(10)) PERSISTED PRIMARY KEY,
	TenHangHoa VARCHAR(255) NOT NULL,
	MaLoaiHangHoa VARCHAR(10) FOREIGN KEY(MaLoaiHangHoa) REFERENCES LOAIHANGHOA(MaLoaiHangHoa) NOT NULL,
	MaNhaCungCap VARCHAR(10) FOREIGN KEY(MaNhaCungCap) REFERENCES NHACUNGCAP(MaNhaCungCap) NOT NULL,
	XuatXu VARCHAR(255) NULL,
	MaVach VARCHAR(50) NULL,
	MaDonViTinh VARCHAR(10) FOREIGN KEY(MaDonViTinh) REFERENCES DONVITINH(MaDonViTinh) NOT NULL,
	ThoiGianBaoHanh int NULL,
	MoTa VARCHAR(MAX) NULL,
	Anh IMAGE NULL,
	ConQuanLy BIT NOT NULL
	)
GO

-- Phần Lịch Trọng --------------------------------------------------------

-- 12 Create table LOAIKHACHHANG
CREATE TABLE LOAIKHACHHANG
	(STT INT IDENTITY NOT NULL,
	MaLoaiKhachHang AS CAST('LKH'+RIGHT('000'+CAST(STT AS VARCHAR(3)), 3) AS VARCHAR(10)) PERSISTED PRIMARY KEY,
	TenLoaiKhachHang VARCHAR(50) NOT NULL,
	DienGiai VARCHAR(MAX) null
	)
GO

-- 13 Create table KHACHHANG
CREATE TABLE KHACHHANG
	(STT INT IDENTITY NOT NULL,
	MaKhachHang AS CAST('KH'+RIGHT('00000000'+CAST(STT AS VARCHAR(8)), 8) AS VARCHAR(10)) PERSISTED PRIMARY KEY,
	TenKhachHang VARCHAR(100) not null,
	MaLoaiKhachHang	VARCHAR(10) FOREIGN KEY(MaLoaiKhachHang) REFERENCES LOAIKHACHHANG(MaLoaiKhachHang) NOT NULL,
	MaKhuVuc VARCHAR(10) FOREIGN KEY(MaKhuVuc) REFERENCES KHUVUC(MaKhuVuc) NOT NULL,
	NgaySinh DATE null,
	DiaChi VARCHAR(MAX) null,
	DienThoai VARCHAR(20),
	Email VARCHAR(100)
	)
GO

-- 14 Create table TRANGTHAI
CREATE TABLE TRANGTHAI
	(STT INT IDENTITY NOT NULL,
	MaTrangThai AS CAST('TT'+RIGHT('00'+CAST(STT AS VARCHAR(2)), 2) AS VARCHAR(10)) PERSISTED PRIMARY KEY,
	TenTrangThai VARCHAR(100) NOT NULL
	)
GO

-- 15 Create Table LOAIDICHVU
CREATE TABLE LOAIDICHVU
	(STT INT IDENTITY NOT NULL,
	MaLoaiDichVu AS CAST('LDV'+RIGHT('00'+CAST(STT AS VARCHAR(2)),2) AS VARCHAR(10)) PERSISTED PRIMARY KEY,
	TenLoaiDichVu VARCHAR(100) NOT NULL,
	DienGiai VARCHAR(MAX) NULL
	)
GO

-- 16 Create table DICHVU
CREATE TABLE DICHVU
	(STT INT IDENTITY NOT NULL,
	MaDichVu AS CAST('DV'+RIGHT('0000'+CAST(STT AS VARCHAR(4)),4) AS VARCHAR(10)) PERSISTED PRIMARY KEY,
	MaLoaiDichVu VARCHAR(10) FOREIGN KEY(MaLoaiDichVu) REFERENCES LOAIDICHVU(MaLoaiDichVu) NOT NULL,
	TenDichVu VARCHAR(100) NOT NULL,
	TienCong MONEY NOT NULL
	)
GO

-- 17 Create table XE
CREATE TABLE XE
	(STT INT IDENTITY NOT NULL,
	BienSoXe VARCHAR(10) PRIMARY KEY NOT NULL,
	SoSuon VARCHAR(50) NOT NULL,
	LoaiXe VARCHAR(100) NOT NULL,
	HangSanXuat VARCHAR(100) NOT NULL,
	DongXe VARCHAR(100)
	)
GO

-- 18 Create Table BAOGIA
CREATE TABLE BAOGIA
	(STT INT IDENTITY NOT NULL,
	MaBaoGia AS CAST('BG'+RIGHT('00000000'+CAST(STT AS VARCHAR(8)),8) AS VARCHAR(10)) PERSISTED PRIMARY KEY,
	NgayLap DATE NOT NULL,
	MaKhachHang VARCHAR(10) FOREIGN KEY(MaKhachHang) REFERENCES KHACHHANG(MaKhachHang) NOT NULL,
	BienSoXe VARCHAR(10) FOREIGN KEY(BienSoXe) REFERENCES XE(BienSoXe) NOT NULL,
	MaNguoiDung VARCHAR(10) FOREIGN KEY(MaNguoiDung) REFERENCES NGUOIDUNG(MaNguoiDung) NOT NULL,
	TongTien MONEY NOT NULL
	)
GO

-- 19 Create table CHITIETBAOGIA
CREATE TABLE CHITIETBAOGIA
	(STT INT IDENTITY NOT NULL,
	MaChiTietBaoGia AS CAST('CTBG'+RIGHT('000000'+CAST(STT AS VARCHAR(6)),6) AS VARCHAR(10)) PERSISTED PRIMARY KEY,
	MaBaoGia VARCHAR(10) FOREIGN KEY(MaBaoGia) REFERENCES BAOGIA(MaBaoGia) NOT NULL,
	MaDichVu VARCHAR(10) FOREIGN KEY(MaDichVu) REFERENCES DICHVU(MaDichVu) NOT NULL,
	MaHangHoa VARCHAR(10) FOREIGN kEY(MaHangHoa) REFERENCES HANGHOA(MaHangHoa) NOT NULL,
	SoLuong INT NOT NULL,
	DonGia MONEY NOT NULL,
	ThanhTien MONEY NOT NULL
	)
GO

--phần Thái-------------------------------------------------------------

-- 20 Create table KHO
CREATE TABLE KHO
	(STT INT IDENTITY NOT NULL,
	MaKho AS CAST('K'+RIGHT('0000'+CAST(STT AS VARCHAR(4)),4) AS VARCHAR(10)) PERSISTED PRIMARY KEY,
	TenKho VARCHAR(255) NOT NULL,
	NguoiLienHe VARCHAR(255) NULL,
	DiaChi VARCHAR(255) NULL,
	Email VARCHAR(255) NULL,
	DienThoai VARCHAR(20) NULL,
	Fax VARCHAR(20) NULL,
	DiDong VARCHAR(20) NULL,
	NguoiQuanLy VARCHAR(255) NULL,
	MoTa VARCHAR(MAX) NULL,
	ConQuanLy BIT NOT NULL
	)
GO

-- 21 Create table TONKHO
CREATE TABLE TONKHO
	(MaKho VARCHAR(10) FOREIGN KEY(MaKho) REFERENCES KHO(MaKho) NOT NULL,
	MaHangHoa VARCHAR(10) FOREIGN KEY(MaHangHoa) REFERENCES HANGHOA(MaHangHoa) NOT NULL,
	TonKhoToiDa INT NULL,
	TonKhoToiThieu INT NULL,
	TonKhoHienTai INT NULL,
	CONSTRAINT PK_TONKHO PRIMARY KEY CLUSTERED (MaKho, MaHangHoa)
	)
GO

-- 22 Create table PHIEUXUATKHO
CREATE TABLE PHIEUXUATKHO
	(STT INT IDENTITY NOT NULL,
	MaPhieuXuatKho AS CAST('PXK'+RIGHT('0000000'+CAST(STT AS VARCHAR(7)),7) AS VARCHAR(10)) PERSISTED PRIMARY KEY,
	NgayXuatKho DATE NOT NULL,
	MaKho VARCHAR(10) FOREIGN KEY(MaKho) REFERENCES KHO(MaKho) NOT NULL,
	MaKhachHang VARCHAR(10) FOREIGN KEY(MaKhachHang) REFERENCES KHACHHANG(MaKhachHang) NOT NULL,
	NguoiLienHe	VARCHAR(20) NOT NULL,
	MaNguoiDung VARCHAR(10) FOREIGN KEY(MaNguoiDung) REFERENCES NGUOIDUNG(MaNguoiDung) NOT NULL,
	LyDo VARCHAR(255) NULL,
	ChietKhau FLOAT NULL,
	ThanhTien MONEY NULL,
	MoTa VARCHAR(MAX) NULL,
	ConQuanLy BIT NOT NULL
	)
GO

-- 23 create table CHITIETPHIEUXUATKHO
CREATE TABLE CHITIETPHIEUXUATKHO
	(STT INT IDENTITY NOT NULL,
	MaPhieuXuatKho VARCHAR(10) FOREIGN KEY(MaPhieuXuatKho) REFERENCES PHIEUXUATKHO(MaPhieuXuatKho) NOT NULL,
	MaHangHoa VARCHAR(10) FOREIGN KEY(MaHangHoa) REFERENCES HANGHOA(MaHangHoa) NOT NULL,
	Thue FLOAT NULL,
	SoLuong INT NOT NULL,
	DonGia MONEY NOT NULL,
	ThanhTien MONEY NOT NULL,
	MoTa VARCHAR(MAX) NULL,
	CONSTRAINT PK_CHITIETPHIEUXUATKHO PRIMARY KEY CLUSTERED (MaPhieuXuatKho, MaHangHoa)
	)
GO

-- 24 Create table PHIEUNHAPKHO
CREATE TABLE PHIEUNHAPKHO
	(STT INT IDENTITY NOT NULL,
	MaPhieuNhapKho AS CAST('PNK'+RIGHT('0000000'+CAST(STT AS VARCHAR(7)),7) AS VARCHAR(10)) PERSISTED PRIMARY KEY,
	NgayNhapKho	DATE NOT NULL,
	MaNguoiDung VARCHAR(10) FOREIGN KEY(MaNguoiDung) REFERENCES NGUOIDUNG(MaNguoiDung) NOT NULL,
	MaKho VARCHAR(10) FOREIGN KEY(MaKho) REFERENCES KHO(MaKho) NOT NULL,
	MaHopDong VARCHAR(10) NOT NULL,
	MaNhaCungCap VARCHAR(10) FOREIGN KEY(MaNhaCungCap) REFERENCES NHACUNGCAP(MaNhaCungCap) NOT NULL,
	MaKhachHang VARCHAR(10) FOREIGN KEY(MaKhachHang) REFERENCES KHACHHANG(MaKhachHang) NOT NULL,
	NguoiDaiDien VARCHAR(MAX) NULL,
	LyDo VARCHAR(MAX) NULL,
	ThanhTien MONEY NULL,
	ChietKhau FLOAT NOT NULL,
	MoTa VARCHAR(MAX) NULL,
	ConQuanLy BIT NOT NULL
	)
GO

-- 25 Create table CHITIETPHIEUNHAPKHO
CREATE TABLE CHITIETPHIEUNHAPKHO
	(STT INT IDENTITY NOT NULL,
	MaPhieuNhapKho VARCHAR(10) FOREIGN KEY(MaPhieuNhapKho) REFERENCES PHIEUNHAPKHO(MaPhieuNhapKho) NOT NULL,
	MaHangHoa VARCHAR(10) FOREIGN KEY(MaHangHoa) REFERENCES HANGHOA(MaHangHoa) NOT NULL,
	Thue FLOAT NULL,
	SoLuong INT NOT NULL,
	DonGia MONEY NOT NULL,
	ThanhTien MONEY NOT NULL,
	CONSTRAINT PK_CHITIETPHIEUNHAPKHO PRIMARY KEY CLUSTERED (MaPhieuNhapKho, MaHangHoa)
	)
GO

-- 26 Create table PHIEUKIEMKE
CREATE TABLE PHIEUKIEMKE
	(STT INT IDENTITY NOT NULL,
	MaPhieuKiemKe AS CAST('PKK'+RIGHT('0000000'+CAST(STT AS VARCHAR(7)),7) AS VARCHAR(10)) PERSISTED PRIMARY KEY,
	NgayKiemKe DATE NOT NULL,
	MaNguoiDung	VARCHAR(10) FOREIGN KEY(MaNguoiDung) REFERENCES NGUOIDUNG(MaNguoiDung) NOT NULL,
	MaKho VARCHAR(10) FOREIGN KEY(MaKho) REFERENCES KHO(MaKho) NOT NULL,
	LyDo VARCHAR(MAX) NULL,
	MoTa VARCHAR(MAX) NULL,
	KetQua VARCHAR(MAX) NOT NULL
	)
GO

-- 27 create table CHITIETPHIEUKIEMKE
CREATE TABLE CHITIETPHIEUKIEMKE
	(MaPhieuKiemKe VARCHAR(10) FOREIGN KEY(MaPhieuKiemKe) REFERENCES PHIEUKIEMKE(MaPhieuKiemKe) NOT NULL,
	MaHangHoa VARCHAR(10) FOREIGN KEY(MaHangHoa) REFERENCES HANGHOA(MaHangHoa) NOT NULL,
	SoLuongTon INT NULL,
	SoLuongKiemKe INT NULL,
	SoLuongChenhLech INT NULL,
	MoTa VARCHAR(MAX) NULL,
	CONSTRAINT PK_CHITIETPHIEUKIEMKE PRIMARY KEY CLUSTERED (MaPhieuKiemKe, MaHangHoa)
	)
GO